package com.sxfq.loansupermarket.backsystem.modular.report.service.impl;

import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SearchWholeHospitalInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.entity.SevenReportInfo;
import com.sxfq.loansupermarket.backsystem.modular.report.service.IGetTotlesService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional
public class GetTotleServicesImpl implements IGetTotlesService {

    public String getTotleSql(SevenReportInfo searchInfo,String classifications) {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        String tableName1 = tableHead + "deal_info";
        String sql = " union all   select '合计' as rirstClassification,\n" +
                " (select count(1) from(\n" +
                " SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='新客首次' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " "+classifications+""+
                " group by a.reservations_id\n" +
                " ) t) AS outpatientVolume,\n" +
                " (select count(1) from (\n" +
                "SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='新客首次' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " "+classifications+""+
                " and b.deal_status='已成交' \n" +
                " group by a.reservations_id\n" +
                " ) t) AS volume, \n" +
                " (SELECT sum(t.getMoney) FROM (\n" +
                "SELECT b.service_actual_pay as getMoney FROM " + tableName + " a  \n" +
                "left join " + tableName1 + " b on a.id=b.customer_id  left join pay_service_info c on b.pay_service_id=c.id\n" +
                "where a.customer_status='新客首次' \n" +
                " and b.get_time>='" + searchInfo.getStartTime() + "'\n" +
                " and b.get_time<='" + searchInfo.getEndTime() + "' \n" +
                "  and b.deal_status='已成交'  "+classifications+""+
                " ) t) AS departmentPerformance  from dual \n" +
                "  union all\n" +
                "  select '合计' as rirstClassification,\n" +
                " (select count(1) from(\n" +
                " SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='新客二次' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " "+classifications+""+
                " group by a.reservations_id\n" +
                " ) t) AS outpatientVolume,\n" +
                " (select count(1) from (\n" +
                "SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='新客二次' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " "+classifications+""+
                " and b.deal_status='已成交' \n" +
                " group by a.reservations_id\n" +
                " ) t) AS volume, \n" +
                " (SELECT sum(t.getMoney) FROM (\n" +
                "SELECT b.service_actual_pay as getMoney FROM " + tableName + " a  \n" +
                "left join " + tableName1 + " b on a.id=b.customer_id  left join pay_service_info c on b.pay_service_id=c.id\n" +
                "where a.customer_status='新客二次' \n" +
                " and b.get_time>='" + searchInfo.getStartTime() + "'\n" +
                " and b.get_time<='" + searchInfo.getEndTime() + "' \n" +
                " and b.deal_status='已成交'  "+classifications+""+
                " ) t) AS departmentPerformance  from dual \n" +
                "  union all \n" +
                "  select '合计' as rirstClassification,\n" +
                "(select count(1) from(\n" +
                " SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status like '%新客%' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " "+classifications+""+
                " group by a.reservations_id\n" +
                " ) t) AS outpatientVolume,\n" +
                " (select count(1) from (\n" +
                "SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status like '%新客%' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " "+classifications+""+
                " and b.deal_status='已成交' \n" +
                " group by a.reservations_id\n" +
                " ) t) AS volume, \n" +
                " (SELECT sum(t.getMoney) FROM (\n" +
                "SELECT b.service_actual_pay as getMoney FROM " + tableName + " a  \n" +
                "left join " + tableName1 + " b on a.id=b.customer_id  left join pay_service_info c on b.pay_service_id=c.id\n" +
                "where a.customer_status like '%新客%' \n" +
                " and b.get_time>='" + searchInfo.getStartTime() + "'\n" +
                " and b.get_time<='" + searchInfo.getEndTime() + "' \n" +
                " and b.deal_status='已成交' "+classifications+""+
                " ) t) AS departmentPerformance  from dual   \n" +
                "  union all\n" +
                "  select '合计' as rirstClassification,\n" +
                " (select count(1) from(\n" +
                " SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='老客' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " "+classifications+""+
                " group by a.reservations_id\n" +
                " ) t) AS outpatientVolume,\n" +
                " (select count(1) from (\n" +
                "SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='老客' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " "+classifications+""+
                " and b.deal_status='已成交' \n" +
                " group by a.reservations_id\n" +
                " ) t) AS volume, \n" +
                " (SELECT sum(t.getMoney) FROM (\n" +
                "SELECT b.service_actual_pay as getMoney FROM " + tableName + " a  \n" +
                "left join " + tableName1 + " b on a.id=b.customer_id  left join pay_service_info c on b.pay_service_id=c.id\n" +
                "where a.customer_status='老客' \n" +
                " and b.get_time>='" + searchInfo.getStartTime() + "'\n" +
                " and b.get_time<='" + searchInfo.getEndTime() + "' \n" +
                "  and b.deal_status='已成交' "+classifications+""+
                " ) t) AS departmentPerformance  from dual \n" +
                " union all\n" +
                " select '合计' as rirstClassification,\n" +
                " (select count(1) from(\n" +
                " SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status!='复诊' and a.customer_status!='预约' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " "+classifications+""+
                " group by a.reservations_id\n" +
                " ) t) AS outpatientVolume,\n" +
                " (select count(1) from (\n" +
                "SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status!='复诊' and a.customer_status!='预约' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " "+classifications+""+
                " and b.deal_status='已成交' \n" +
                " group by a.reservations_id\n" +
                " ) t) AS volume, \n" +
                " (SELECT sum(t.getMoney) FROM (\n" +
                "SELECT b.service_actual_pay as getMoney FROM " + tableName + " a  \n" +
                "left join " + tableName1 + " b on a.id=b.customer_id  left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status!='复诊' and a.customer_status!='预约' \n" +
                " and b.get_time>='" + searchInfo.getStartTime() + "'\n" +
                " and b.get_time<='" + searchInfo.getEndTime() + "' \n" +
                " and b.deal_status='已成交' "+classifications+""+
                " ) t) AS departmentPerformance  from dual \n" +
                "  ";
        return sql;
    }

    public String getsql(SevenReportInfo searchInfo, String classification, String condition, int i, List<SevenReportInfo> list1) {
        String sql = "";
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        String tableName1 = tableHead + "deal_info";
        sql = " select '" + classification + "' as rirstClassification,\n" +
                " (select count(1) from(\n" +
                " SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='新客首次' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + " \n" +
                " group by a.reservations_id\n" +
                " ) t) AS outpatientVolume,\n" +
                " (select count(1) from (\n" +
                "SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='新客首次' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + " \n" +
                " and b.deal_status='已成交' \n" +
                " group by a.reservations_id\n" +
                " ) t) AS volume, \n" +
                " (SELECT sum(t.getMoney) FROM (\n" +
                "SELECT b.service_actual_pay as getMoney FROM " + tableName + " a  \n" +
                "left join " + tableName1 + " b on a.id=b.customer_id  left join pay_service_info c on b.pay_service_id=c.id\n" +
                "where a.customer_status='新客首次' \n" +
                " and b.get_time>='" + searchInfo.getStartTime() + "'\n" +
                " and b.get_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + "  and b.deal_status='已成交'  \n" +
                " ) t) AS departmentPerformance  from dual \n" +
                "  union all\n" +
                "  select '" + classification + "' as rirstClassification,\n" +
                " (select count(1) from(\n" +
                " SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='新客二次' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + " \n" +
                " group by a.reservations_id\n" +
                " ) t) AS outpatientVolume,\n" +
                " (select count(1) from (\n" +
                "SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='新客二次' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + " \n" +
                " and b.deal_status='已成交' \n" +
                " group by a.reservations_id\n" +
                " ) t) AS volume, \n" +
                " (SELECT sum(t.getMoney) FROM (\n" +
                "SELECT b.service_actual_pay as getMoney FROM " + tableName + " a  \n" +
                "left join " + tableName1 + " b on a.id=b.customer_id  left join pay_service_info c on b.pay_service_id=c.id\n" +
                "where a.customer_status='新客二次' \n" +
                " and b.get_time>='" + searchInfo.getStartTime() + "'\n" +
                " and b.get_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + "  and b.deal_status='已成交'  \n" +
                " ) t) AS departmentPerformance  from dual \n" +
                "  union all \n" +
                "    select '" + classification + "' as rirstClassification,\n" +
                " (select count(1) from(\n" +
                " SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status like '%新客%' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + " \n" +
                " group by a.reservations_id\n" +
                " ) t) AS outpatientVolume,\n" +
                " (select count(1) from (\n" +
                "SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status like '%新客%' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + " \n" +
                " and b.deal_status='已成交' \n" +
                " group by a.reservations_id\n" +
                " ) t) AS volume, \n" +
                " (SELECT sum(t.getMoney) FROM (\n" +
                "SELECT b.service_actual_pay as getMoney FROM " + tableName + " a  \n" +
                "left join " + tableName1 + " b on a.id=b.customer_id  left join pay_service_info c on b.pay_service_id=c.id\n" +
                "where a.customer_status like '%新客%' \n" +
                " and b.get_time>='" + searchInfo.getStartTime() + "'\n" +
                " and b.get_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + "  and b.deal_status='已成交'  \n" +
                " ) t) AS departmentPerformance  from dual   \n" +
                "  union all\n" +
                "  select '" + classification + "' as rirstClassification,\n" +
                " (select count(1) from(\n" +
                " SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='老客' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + " \n" +
                " group by a.reservations_id\n" +
                " ) t) AS outpatientVolume,\n" +
                " (select count(1) from (\n" +
                "SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status='老客' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + " \n" +
                " and b.deal_status='已成交' \n" +
                " group by a.reservations_id\n" +
                " ) t) AS volume, \n" +
                " (SELECT sum(t.getMoney) FROM (\n" +
                "SELECT b.service_actual_pay as getMoney FROM " + tableName + " a  \n" +
                "left join " + tableName1 + " b on a.id=b.customer_id  left join pay_service_info c on b.pay_service_id=c.id\n" +
                "where a.customer_status='老客' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + "  and b.deal_status='已成交'  \n" +
                " ) t) AS departmentPerformance  from dual \n" +
                " union all\n" +
                " select '" + classification + "' as rirstClassification,\n" +
                " (select count(1) from(\n" +
                " SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status!='复诊' and a.customer_status!='预约' \n" +
                " and b.get_time>='" + searchInfo.getStartTime() + "'\n" +
                " and b.get_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + " \n" +
                " group by a.reservations_id\n" +
                " ) t) AS outpatientVolume,\n" +
                " (select count(1) from (\n" +
                "SELECT a.reservations_id FROM  " + tableName + " a left join " + tableName1 + " b on a.id=b.customer_id left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status!='复诊' and a.customer_status!='预约' \n" +
                " and a.arrival_time>='" + searchInfo.getStartTime() + "'\n" +
                " and a.arrival_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + " \n" +
                " and b.deal_status='已成交' \n" +
                " group by a.reservations_id\n" +
                " ) t) AS volume, \n" +
                " (SELECT sum(t.getMoney) FROM (\n" +
                "SELECT b.service_actual_pay as getMoney FROM " + tableName + " a  \n" +
                "left join " + tableName1 + " b on a.id=b.customer_id  left join pay_service_info c on b.pay_service_id=c.id\n" +
                " where a.customer_status!='复诊' and a.customer_status!='预约' \n" +
                " and b.get_time>='" + searchInfo.getStartTime() + "'\n" +
                " and b.get_time<='" + searchInfo.getEndTime() + "' \n" +
                " and " + condition + "  and b.deal_status='已成交'  \n" +
                " ) t) AS departmentPerformance  from dual \n" +
                "  ";
        if (i == list1.size() - 1) {
        } else {
            sql += " union all ";
        }
        return sql;
    }

}



